﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using NPOI.SS.Formula.Functions;
using XiAnAirport.BUS;
using XiAnAirport.Models;

namespace XiAnAirport.DAL
{
    public class MenuFOptionDAL
    {
        public string AddMenu(Menu menuinfo)
        {
            string strSql = string.Format("INSERT INTO menu (name,icon,url,parentId,CreationDate,creationPerson,isDelete)VALUES('{0}','{1}','{2}',{3},'{4}','{5}',0)"
                , menuinfo.name, menuinfo.icon, menuinfo.url, menuinfo.parentId, DateTime.Now.ToString(), menuinfo.creationPerson);

            try
            {
                int count = SqlHelper<Menu>.SqlExecuteRows(strSql);

                if (count > 0)
                {
                    return "success";
                }
                else
                {
                    return "fail";
                }
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public string EditMenu(Menu menuinfo)
        {
            string strSql = string.Format("UPDATE menu SET name='{0}',icon='{1}',url='{2}',parentId={3} WHERE id={4}"
                , menuinfo.name, menuinfo.icon, menuinfo.url, menuinfo.parentId, menuinfo.id);
            try
            {
                int count = SqlHelper<Menu>.SqlExecuteRows(strSql);

                if (count >= 0)
                {
                    return "success";
                }
                else
                {
                    return "fail";
                }
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public string DelMenu(int menuid)
        {
            string strSql = "delete FROM menu WHERE id=" + menuid;

            try
            {
                int menucount = SqlHelper<Menu>.SqlExecuteRows(strSql);
                if (menucount > 0)
                {
                    return "success";
                }
                return "fail";
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public string BatchDelMenu(string ids)
        {
            string strSql = "delete FROM menu WHERE id in ({0})" + ids;

            try
            {
                int menucount = SqlHelper<Menu>.SqlExecuteRows(strSql);
                if (menucount > 0)
                {
                    return "success";
                }
                return "fail";
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public bool CheckMenuExist(int menuid)
        {
            string strSql = string.Format("SELECT COUNT(1) FROM foption WHERE isDelete=0 AND menuID={0} ", menuid);

            try
            {
                int count = SqlHelper<Menu>.SqlScalar(strSql);

                if (count > 0)
                {
                    return true;
                }
            }
            catch (Exception e)
            {
                return true;
            }
            return false;
        }
        public string AddFOption(FOption foptioninfo)
        {
            string strSql = string.Format("INSERT INTO foption (menuID,`function`,functionName,creationDate,creationPerson,isDelete)VALUES({0},'{1}','{2}','{3}','{4}',0)"
                , foptioninfo.menuID, foptioninfo.function, foptioninfo.functionName, DateTime.Now.ToString(), foptioninfo.creationPerson);

            try
            {
                int count = SqlHelper<FOption>.SqlExecuteRows(strSql);
                if (count > 0)
                {
                    return "success";
                }
                return "fail";
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public string EditFOption(FOption foptioninfo)
        {
            string strSql = string.Format("UPDATE foption SET menuID={0},`function`='{1}',functionName='{2}' WHERE id={3}"
                , foptioninfo.menuID, foptioninfo.function, foptioninfo.functionName, foptioninfo.id);
            try
            {
                int count = SqlHelper<Menu>.SqlExecuteRows(strSql);

                if (count >= 0)
                {
                    return "success";
                }
                else
                {
                    return "fail";
                }
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public string DelFOption(int foptionid)
        {
            string strSql = "UPDATE foption SET isDelete =1 WHERE id=" + foptionid;
            try
            {
                int count = SqlHelper<Menu>.SqlExecuteRows(strSql);

                if (count >= 0)
                {
                    return "success";
                }
                else
                {
                    return "fail";
                }
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public List<Menu> QueryMenuByRole(UserInfo userinfo)
        {
            try
            {
                List<Menu> list = new List<Menu>();
                string strSql = string.Empty;
                if (userinfo.username == "fstar")
                {
                    strSql = "SELECT * FROM menu WHERE isDelete=0";
                }
                else
                {
                    strSql = "SELECT * FROM menu WHERE FIND_IN_SET(id ,(SELECT menuRole FROM role WHERE id=@roleid)) AND isDelete=0";
                }
                using (MySqlConnection connection = DataFactory.GetMysqlConnection())
                {
                    MySqlCommand mySqlCommand = new MySqlCommand(strSql, connection);

                    // 添加参数
                    mySqlCommand.Parameters.AddWithValue("@roleid", userinfo.roleid);
                    MySqlDataAdapter adapter = new MySqlDataAdapter(mySqlCommand);
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);
                    DataTable dataTable = dataSet.Tables[0];
                    list = ToList<Menu>(dataTable);
                }
                return list;
            }
            catch
            {
                return null;
            }

        }

        public int QueryMenuByRoleCount(UserInfo userinfo)
        {
            string strSql = string.Format("SELECT COUNT(1) FROM menu WHERE FIND_IN_SET(id ,(SELECT menuRole FROM role WHERE id={0}))", userinfo.roleid);
            int count = 0;

            try
            {
                count = SqlHelper<Menu>.SqlScalar(strSql);
            }
            catch (Exception e)
            {
                return count;
            }
            return count;
        }

        public Menu LoadMenu(int menuid)
        {
            string strSql = "SELECT * FROM menu WHERE id=" + menuid;
            return SqlHelper<Menu>.SqlSingleData(strSql);
        }

        public FOption LoadFOption(int optionid)
        {
            string strSql = "SELECT * FROM foption WHERE id=" + optionid;
            return SqlHelper<FOption>.SqlSingleData(strSql);
        }

        public List<FOption> QueryFOptionByMenuID(int menuid)
        {
            try
            {
                List<FOption> list = new List<FOption>();

                string strSql = "SELECT * FROM foption WHERE isDelete=0 AND menuID=@menuID";
                using (MySqlConnection connection = DataFactory.GetMysqlConnection())
                {
                    MySqlCommand mySqlCommand = new MySqlCommand(strSql, connection);

                    // 添加参数
                    mySqlCommand.Parameters.AddWithValue("@menuID", menuid);
                    MySqlDataAdapter adapter = new MySqlDataAdapter(mySqlCommand);
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);
                    DataTable dataTable = dataSet.Tables[0];
                    list = ToList<FOption>(dataTable);
                }
                return list;
            }
            catch
            {
                return null;
            }

        }

        public List<Menu> QueryMenu(Menu menuinfo)
        {
            List<Menu> list = new List<Menu>();
            try
            {
                string strSql = "SELECT * FROM menu";
                string sqlwhere = string.Empty;
                if (menuinfo.parentId > -1)
                {
                    sqlwhere += " parentId =@parentId AND";
                }
                if (!string.IsNullOrEmpty(menuinfo.name))
                {
                    sqlwhere += " name LIKE @name AND";
                }
                if (sqlwhere.Length > 0)
                {
                    strSql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4);
                }
                if (menuinfo.Recordhead > -1 && menuinfo.Recordtail > 0)
                {
                    strSql += " LIMIT @Recordhead,@Recordtail";
                }

                using (MySqlConnection connection = DataFactory.GetMysqlConnection())
                {
                    MySqlCommand mySqlCommand = new MySqlCommand(strSql, connection);

                    string likeno = "%" + menuinfo.name + "%";
                    mySqlCommand.Parameters.AddWithValue("@parentId", menuinfo.parentId);
                    mySqlCommand.Parameters.AddWithValue("@name", $"%{likeno}%");
                    mySqlCommand.Parameters.AddWithValue("@Recordhead", menuinfo.Recordhead);
                    mySqlCommand.Parameters.AddWithValue("@Recordtail", menuinfo.Recordtail);
                    MySqlDataAdapter adapter = new MySqlDataAdapter(mySqlCommand);
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);
                    DataTable dataTable = dataSet.Tables[0];
                    list = ToList<Menu>(dataTable);
                }
            }
            catch
            {
                return null;
            }

            return list;
        }

        public int QueryMenuCount(Menu menuinfo)
        {
            int count = 0;

            try
            {
                string strSql = "SELECT COUNT(1) FROM menu";
                string sqlwhere = string.Empty;
                if (menuinfo.parentId > -1)
                {
                    sqlwhere += " parentId =@parentId AND";
                }
                if (!string.IsNullOrEmpty(menuinfo.name))
                {
                    sqlwhere += " name LIKE @name AND";
                }
                if (sqlwhere.Length > 0)
                {
                    strSql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4);
                }

                using (MySqlConnection connection = DataFactory.GetMysqlConnection())
                {
                    MySqlCommand mySqlCommand = new MySqlCommand(strSql, connection);

                    // 添加参数
                    string likeno = "%" + menuinfo.name + "%";
                    mySqlCommand.Parameters.AddWithValue("@parentId", menuinfo.parentId);
                    mySqlCommand.Parameters.AddWithValue("@name", $"%{likeno}%");
                    MySqlDataAdapter adapter = new MySqlDataAdapter(mySqlCommand);
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);
                    count = Convert.ToInt32(dataSet.Tables[0].Rows[0][0]);
                }
            }
            catch (Exception e)
            {
                return count;
            }
            return count;
        }

        public List<FOption> QueryOptionByUserID(int userid, string Url)
        {
            try
            {
                List<FOption> list = new List<FOption>();
                UserRoleDAL uda = new UserRoleDAL();
                UserInfo info = uda.LoadUser(userid);
                string strSql = string.Empty;
                if (info.username == "system")
                {
                    strSql = "SELECT a.* FROM foption a LEFT JOIN menu b ON a.menuID=b.id WHERE b.url=@url AND a.isDelete=0 AND b.isDelete=0";
                }
                else
                {
                    strSql = "SELECT a.* FROM foption a LEFT JOIN menu b ON a.menuID=b.id WHERE FIND_IN_SET(a.id ,(SELECT FunctionOption FROM `user` WHERE id=@userid)) AND b.url=@url AND a.isDelete=0 AND b.isDelete=0";
                }

                using (MySqlConnection connection = DataFactory.GetMysqlConnection())
                {
                    MySqlCommand mySqlCommand = new MySqlCommand(strSql, connection);

                    // 添加参数
                    mySqlCommand.Parameters.AddWithValue("@url", Url);
                    mySqlCommand.Parameters.AddWithValue("@userid", userid);
                    MySqlDataAdapter adapter = new MySqlDataAdapter(mySqlCommand);
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);
                    DataTable dataTable = dataSet.Tables[0];
                    list = ToList<FOption>(dataTable);
                }
                return list;
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        public List<FOption> QueryOptionByUserID(int menuid)
        {
            string strSql = "select * FROM foption WHERE menuID=" + menuid + " AND isDelete=0";

            return SqlHelper<FOption>.SqlList(strSql);
        }

        public List<T> ToList<T>(DataTable dt) where T : class, new()
        {
            List<T> list = new List<T>();
            //确认参数有效,若无效则返回Null
            if (dt == null)
                return list;
            else if (dt.Rows.Count == 0)
                return list;
            Dictionary<string, string> dicField = new Dictionary<string, string>();
            Dictionary<string, string> dicProperty = new Dictionary<string, string>();
            Type type = typeof(T);

            //创建字段字典，方便查找字段名
            foreach (var item in type.GetFields())
            {
                dicField.Add(item.Name.ToLower(), item.Name);
            }
            //创建属性字典，方便查找属性名
            foreach (var item in type.GetProperties())
            {
                dicProperty.Add(item.Name.ToLower(), item.Name);
            }

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                //创建泛型对象
                T _t = Activator.CreateInstance<T>();
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    string memberKey = dt.Columns[j].ColumnName.ToLower();

                    //字段赋值
                    if (dicField.ContainsKey(memberKey))
                    {
                        FieldInfo theField = type.GetField(dicField[memberKey]);
                        var dbValue = dt.Rows[i][j];
                        if (dbValue.GetType() == typeof(DBNull))
                            dbValue = null;
                        if (dbValue != null)
                        {
                            Type memberType = theField.FieldType;
                            //dbValue = dbValue.ChangeType(memberType);
                            dbValue = Convert.ChangeType(dbValue, memberType);
                        }
                        theField.SetValue(_t, dbValue);
                    }
                    //属性赋值
                    if (dicProperty.ContainsKey(memberKey))
                    {
                        PropertyInfo theProperty = type.GetProperty(dicProperty[memberKey]);
                        if (!theProperty.CanWrite) continue;
                        var dbValue = dt.Rows[i][j];
                        if (dbValue.GetType() == typeof(DBNull))
                            dbValue = null;
                        if (dbValue != null)
                        {
                            Type memberType = theProperty.PropertyType;
                            if (memberType.IsGenericType && memberType.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
                            {
                                NullableConverter newNullableConverter = new NullableConverter(memberType);
                                dbValue = newNullableConverter.ConvertFrom(dbValue);
                            }
                            else
                            {
                                dbValue = Convert.ChangeType(dbValue, memberType);
                            }
                        }
                        theProperty.SetValue(_t, dbValue, null);
                    }
                }
                list.Add(_t);
            }
            return list;
        }
    }
}
